﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.Linq;
using System.Runtime.CompilerServices;

namespace Susam
{
    class SusamDS
    {
        private static susamDataSet ds;
        private SusamDS()
        {
        }

        public static susamDataSet DataSet
        {
            get
            {
                if (ds == null)
                {
                    ds = new susamDataSet();
                }
                return ds;
            }
        }
    }

    class DatabaseManager
    {
        private static DatabaseManager instance;
        private DatabaseManager ()
	    {
            //SqlConnection cn = new SqlConnection();
            //SqlDataAdapter da;
            // SqlCommandBuilder cmdBuilder;

            //Set the connection string of the SqlConnection object to connect
            //to the SQL Server database in which you created the sample
            //table.
            //cn.ConnectionString = Properties.Settings.Default.susamConnectionString;
            //cn.Open();

            //da = new SqlDataAdapter("select * from CustTest order by CustId", cn);


        }

        public static DatabaseManager Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new DatabaseManager();
                }
                return instance;
            }
        }

        public IEnumerable<susamDataSet.tblPersonRow> GetPeopleList()
        {
            var ds = SusamDS.DataSet;

            var people =
                from person in ds.tblPerson
                orderby person.PersonName
                select person;

            return people;
        }

        public IEnumerable<susamDataSet.tblPersonRow> GetPeopleList(IEnumerable<String> roles)
        {
            var ds = SusamDS.DataSet;

            var peopleIDs =
                (from jnc in ds.jncBookAuthor
                 select jnc.AuthorID).Distinct();

            peopleIDs = peopleIDs.Union(GetContributorsWithRoles(roles));

            var people =
                from person in ds.tblPerson
                orderby person.PersonName
                where peopleIDs.Contains(person.PersonID)
                select person;

            return people;
        }

        private IEnumerable<int> GetContributorsWithRoles(IEnumerable<String> roles)
        {
            var ds = SusamDS.DataSet;

            var contributors = 
                (from jnc in ds.jncBookContributor
                 where roles.Contains(jnc.ContributorRole)
                select jnc.ContributorID).Distinct();

            return contributors;
        }

        public IEnumerable<susamDataSet.tblPublisherRow> GetPublisherList()
        {
            var ds = SusamDS.DataSet;

            var publishers =
                from publisher in ds.tblPublisher
                orderby publisher.PublisherName
                select publisher;

            return publishers;
        }

        public IEnumerable<susamDataSet.tblLanguageRow> GetLanguageList()
        {
            var ds = SusamDS.DataSet;

            var languages =
                from language in ds.tblLanguage
                orderby language.LanguageName
                select language;

            return languages;
        }

        public IEnumerable<susamDataSet.tblFileTypeRow> GetFileTypeList()
        {
            var ds = SusamDS.DataSet;

            var fileTypes =
                from type in ds.tblFileType
                orderby type.FileTypeName
                select type;

            return fileTypes;
        }

        public IEnumerable<BookDisplay> GetFullBookList()
        {
            List<BookDisplay> bd = new List<BookDisplay>();

            var ds = SusamDS.DataSet;

            var bookRows =
                from book in ds.tblBook
                select book;

            return ConvertBookRowsToBookDisplay(bookRows);
        }

        public int GetBookCount()
        {
            var ds = SusamDS.DataSet;

            var count =
                (from book in ds.tblBook
                select book).Count();

            return count;
        }

        public IEnumerable<BookDisplay> ConvertBookIDsToBookDisplay(IEnumerable<int> bookIDs)
        {
            var ds = SusamDS.DataSet;

            var books =
                from book in ds.tblBook
                where bookIDs.Contains(book.BookID)
                select book;

            return ConvertBookRowsToBookDisplay(books);
        }

        private IEnumerable<BookDisplay> ConvertBookRowsToBookDisplay(IEnumerable<susamDataSet.tblBookRow> bookRows)
        {
            List<BookDisplay> bookDisplayList = new List<BookDisplay>();

            foreach (var row in bookRows)
            {
                BookDisplay book = new BookDisplay();
                book.ID = row.BookID;
                book.Title = row.BookTitle;
                book.Type = new FileTypeAttribute().GetAttributeListforBookAsString(row.BookID);
                book.URI = row.BookURI;
                book.Authors = new AuthorAttribute().GetAttributeListforBookAsString(row.BookID);
                book.Contributors = new ContributorAttribute().GetAttributeListforBookAsString(row.BookID);
                book.Publishers = new PublisherAttribute().GetAttributeListforBookAsString(row.BookID);
                book.Language = new LanguageAttribute().GetAttributeListforBookAsString(row.BookID);

                bookDisplayList.Add(book);
            }

            return bookDisplayList;
        }

        [MethodImpl(MethodImplOptions.Synchronized)]
        public void Update(Metadata meta)
        {
            // TODO: All tables except for language use the same logic. Refactor?

            // Books
            var books = new BookTable();
            int bookID = books.Add(meta);

            // Authors
            if (meta.authorList.Any())
            {
                var authorTable = new PersonTable();
                var jBookAuthor = new BookAuthorJunction();
                foreach (var author in meta.authorList)
                {
                    // All creators are indexed as Author independent of the role
                    var authorID = authorTable.Add(author.name);
                    Debug.WriteLine("Author ID: {0}", authorID);
                    // Book - People Junction
                    jBookAuthor.AddMatch(bookID, authorID);
                } 
            }

            // Contributors
            if (meta.contributorList.Any())
            {
                var contributorTable = new PersonTable();
                var jBookContributor = new BookContributorJunction();
                foreach (var contributor in meta.contributorList)
                {
                    var contributorID = contributorTable.Add(contributor.name);
                    Debug.WriteLine("Contributor ID: {0}", contributorID);
                    // Book - People Junction
                    jBookContributor.AddMatch(bookID, contributorID, contributor.role);
                }
            }

            // Publishers
            if (meta.publisherList.Any())
            {
                var publisherTable = new PublisherTable();
                var jBookPublisher = new BookPublisherJunction();
                foreach (var publisher in meta.publisherList)
                {
                    var pubID = publisherTable.Add(publisher);
                    // Book - Publisher Junction
                    jBookPublisher.AddMatch(bookID, pubID);
                }
            }

            // Languages
            if (meta.languageList.Any())
            {
                var languageTable = new LanguageTable();
                var jBookLang = new BookLanguageJunction();
                foreach (var language in meta.languageList)
                {
                    String langDisplayName;
                    try
                    {
                        CultureInfo ci = new CultureInfo(language);
                        langDisplayName = ci.DisplayName;
                    }
                    catch
                    {
                        langDisplayName = "Unrecognized";
                    }

                    var langID = languageTable.Add(langDisplayName);
                    // Book - Language Junction
                    jBookLang.AddMatch(bookID, langID);
                }
            }


            // File Types
            if (meta._file.fileType != null) 
            {
                var fileTypes = new FileTypeTable();
                var jBookFileType = new BookFileTypeJunction();
                var typeID = fileTypes.Add(meta._file.fileType);
                Debug.WriteLine("Type ID: {0}", typeID);
                // Book - File Type Junction
                jBookFileType.AddMatch(bookID, typeID);
            }

        }
    }
}
